# -*- coding: utf-8 -*-

''' 基本的批量数据操作
 
    SqlStore: 基本上以纯sql实现
    NormalStore: 以Django Orm实现
    这里有两个问题:
        1.  sqlite的事务独占访问问题. 当django/sql事务未完成时，sqlite文件不能以写模式再次打开
        2.  如果SqlStore的Conn不是从Django得到的，而是直接针对db文件打开。则可能一个较大跨度中的操作不是在同一个连接中，
            也相当于在两个互相隔离的事务里，这些操作的相互直接的逻辑影响无法得到(比如先同步code在导入数据).
            这个问题比较严重。因此，需要完全基于sql或完全基于django(但可以从中得到connection)的解决方案
        目前，传入到SqlStore的Conn,也必须是从Django得到的。否则有逻辑问题。
        但是，django的conn使用方式比原始的connection大概要慢3倍

'''

import logging
import sqlite3

from django.db import transaction

from wolfox.foxit.dune.models import StockCode,Corporation,StockCodeHistory,Mapping,SDayQuote,XDayQuote,PowerInfo,Report,CatalogSubject,Catalog
from wolfox.foxit.base.common import createQuoteFromDb,createXInfoFromDb,DataObject


logger = logging.getLogger('foxit.dune.store')

class SqlStore(object):
    '''sql实现,只处理日行情数据

    '''
    def __init__(self,conn):
        self.conn = conn

    def query(self,conn,sql):
        cursor = conn.cursor()
        cursor.execute(sql)
        return cursor.fetchall()

    def query2(self,conn,sql,*args):     #特定于django,即这个函数的sql中只支持format方式,并且只支持%s
        cursor = conn.cursor()
        cursor.execute(sql,args)
        return cursor.fetchall()

    def get_codes_id_map(self,conn):
        id_codes = self.query(conn,"select id,code from dune_stockcode")
        code2id = dict([(r[1],r[0]) for r in id_codes])
        id2code = dict([(r[0],r[1]) for r in id_codes])
        return code2id,id2code

    def get_id2code(self):  #初始化不能在__init__中，会导致django测试问题,因为只是全局初始化一次，因此竞争情况较少出现
        try:
            return self.id2code
        except:
            self.reset_code_id_mapping(self.conn)
        return self.id2code

    def get_code2id(self):
        try:
            return self.code2id
        except:
            self.reset_code_id_mapping(self.conn)
        return self.code2id

    def reset_code_id_mapping(self,conn):
        self.code2id,self.id2code = self.get_codes_id_map(conn)

    def organize_tuples2quotes(self,quotes):
        ''' 将fetchall返回的9元组记录集合转换为Quote，并按照tstock组织成dict
            这样就不需要在order by中添加code了
        '''
        records_map = {}
        for tstock,tdate,topen,tclose,thigh,tlow,tavg,tvolume,tamount in quotes:
            cur = createQuoteFromDb(tstock,tdate,topen,tclose,thigh,tlow,tvolume,tamount,tavg) 
            records_map.setdefault(tstock,[]).append(cur)
        return records_map

    def organize_tuples2quotes2(self,quotes):
        ''' 将fetchall返回的9元组记录集合转换为Quote，并按照tstock组织成dict
            按照已经按tstock,date排序的方式走
            效率提高很大
        '''
        records_map = {}
        curlist = None
        curstock = ''
        for tstock,tdate,topen,tclose,thigh,tlow,tavg,tvolume,tamount in quotes:
            cur = createQuoteFromDb(tstock,tdate,topen,tclose,thigh,tlow,tvolume,tamount,tavg) 
            if not curlist or curstock != tstock:
                curlist = records_map.setdefault(tstock,[])
                curstock = tstock
            curlist.append(cur)
        return records_map

    def organize_tuples2quotes2_b(self,quotes):
        ''' 将fetchall返回的9元组记录集合按照tstock组织成dict,不再做转换
            按照已经按tstock,date排序的方式走
            因为去掉了生成记录的部分，效率提高很大
        '''
        records_map = {}
        curlist = None
        curstock = ''
        for quote in quotes:
            if not curlist or curstock != quote[0]:
                curlist = records_map.setdefault(quote[0],[])
                curstock = quote[0]
            curlist.append(quote)
        return records_map

    def organize_tuples2quotes2_c(self,quotes):
        ''' 将fetchall返回的9元组记录集合按照tstock组织成dict,不再做转换
            按照已经按tstock,date排序的方式走
            效率和b比没有提高,估计list的extend实现不比append高效。
        '''
        records_map = {}
        if not quotes:
            return records_map
        curstock = quotes[0][0]
        i,k=0,0
        for quote in quotes:
            if curstock != quote[0]:
                curlist = records_map.setdefault(curstock,[])
                curlist.extend(quotes[k:i])     
                curstock = quote[0]
                k=i
            i+=1
        curlist = records_map.setdefault(quotes[-1][0],[])
        curlist.extend(quotes[k:])  #最后一组
        return records_map

    remove_squotes_sql = "delete from dune_sdayquote where tdate between %d and %d"
    def remove_squotes(self,conn,begin=0,end=99999999):
        ''' 删除指定时间范围内的的原始记录. 

            删除时间范围 [begin,end)
        '''
        cursor = conn.cursor()
        cursor.execute(self.remove_squotes_sql % (begin,end))
        #cursor.close() #django文档中没有指出需要关闭这个cursor

    def remove_squotes2(self,conn,stocks,begin=0,end=99999999):
        ''' 删除指定stocks,时间范围内的原始记录. 

            stocks为stockname列表
            删除时间范围 [begin,end)
        '''
        condition = ' and sstock in (%s)' % ','.join(["'%s'" % stock for stock in stocks])  #如果stocks为空，则为(),也符合sql语法        
        cursor = conn.cursor()
        cursor.execute((self.remove_squotes_sql + condition) % (begin,end))

    def remove_xquotes(self,conn,begin=0,end=99999999):
        ''' 删除指定时间范围内的的Xed记录. 

            source=1为分析家
            删除时间范围 [begin,end)
        '''
        cursor = conn.cursor()
        cursor.execute('delete from dune_xdayquote where tdate between %d and %d - 1' % (begin,end))
        #cursor.close() #django文档中没有指出需要关闭这个cursor

    def remove_xquotes2(self,conn,stocks,begin=0,end=99999999):
        ''' 从dune_xdayquote删除数据

            stocks为stock_codes
            source=1为分析家
            删除时间范围 [begin,end)
        '''
        cursor = conn.cursor()
        subquery = 'select id from dune_stockcode where code in (%s)' % ','.join(["'%s'" % stock for stock in stocks])  #如果stocks为空，则为(),也符合sql语法                
        cursor.execute('delete from dune_xdayquote where tstock_id in (%s) and tdate between %d and %d-1' % (subquery,begin,end))

    def get_latest_squote_day(self,conn,period='day'):
        cursor = conn.cursor()
        cursor.execute('select max(tdate) from dune_s'+period+'quote')
        return cursor.fetchone()[0] or 0    #如果为None则返回0. 当不存在符合条件的纪录时,sqlite3返回的是None

    def get_latest_xquote_day(self,conn,period='day'):
        cursor = conn.cursor()
        cursor.execute('select max(tdate) from dune_x'+period+'quote')
        return cursor.fetchone()[0] or 0    #如果为None则返回0. 当不存在符合条件的纪录时,sqlite3返回的是None
    
    def get_latest_hour_squote_day(self,conn):
        return self.get_latest_squote_day(conn,period='hour')/100

    def get_latest_hour_xquote_day(self,conn):
        return self.get_latest_xquote_day(conn,period='hour')/100

    def get_latest_xquote_close(self,conn,stock,dfrom='29000101'):
        cursor = conn.cursor()
        cursor.execute('select tclose from dune_xdayquote where tdate = (select max(tdate) from dune_xdayquote where tdate < %d and tstock_id=%d) and tstock_id=%d' % (dfrom,stock,stock))
        tcs = cursor.fetchone()
        return tcs[0] if tcs else 0 #如果为None则返回0. 当不存在符合条件的纪录时,sqlite3返回的是None

    def add_squotes(self,conn,quotes,period='day'):
        ''' 成批导入数据(pyformat风格). 功能同add_squotes_tuple
            
            quotes为元素是tcommon.Quote类型对象的列表
            source_id=1是分析家数据源
            由调用方决定事先是否需要先执行同时间段的清理工作
            这类直接的sql字符串实现的方式,性能上和安全性上不如类似于prepared statement的方式,但是消除了不同的驱动以及django自身所带来的parameters style不同的问题
        '''
        #logger.debug('get cursor......')
        cursor = conn.cursor()
        sql = "insert into dune_s"+period+"quote(sstock,tdate,topen,tclose,thigh,tlow,tavg,tvolume,tamount) values('%(tstock)s',%(tdate)s,%(topen)s,%(tclose)s,%(thigh)s,%(tlow)s,%(tavg)s,%(tvolume)s,%(tamount)s)"
        #logger.debug('begin add quote......')
        for nth,quote in enumerate(quotes):    
            #print u'导入第%d条:%s' % (nth+1,quote)
            #if (nth + 1)%1000 == 1: 
            #    logger.debug(u'导入第%d条:%s',nth+1,quote)
            cursor.execute(sql % quote)
        #cursor.close() #django文档中没有指出需要关闭这个cursor

    def add_hour_squotes(self,conn,quotes):
        self.add_squotes(conn,quotes,period='hour')

    def add_quotes(self,conn,quotes,period='day'):
        ''' 成批导入数据(pyformat风格). 
            由调用方决定事先是否需要先执行同时间段的清理工作
            对存在对应stockcode记录的quote,转移到xdayquote
            反之转移到sdayquote

            这类直接的sql字符串实现的方式,性能上和安全性上不如类似于prepared statement的方式,但是消除了不同的驱动以及django自身所带来的parameters style不同的问题
        '''
        logger.debug('get cursor......')
        cursor = conn.cursor()
        ssql = "insert into dune_s"+period+"quote(sstock,tdate,topen,tclose,thigh,tlow,tavg,tvolume,tamount) values( '%(tstock)s',%(tdate)s,%(topen)s,%(tclose)s,%(thigh)s,%(tlow)s,%(tavg)s,%(tvolume)s,%(tamount)s)"
        xsql = "insert into dune_x"+period+"quote(tstock_id,tdate,topen,tclose,thigh,tlow,tavg,tvolume,tamount)  values( '%(tstock)s',%(tdate)s,%(topen)s,%(tclose)s,%(thigh)s,%(tlow)s,%(tavg)s,%(tvolume)s,%(tamount)s)"
        logger.debug('begin add quote......')
        #print 'code...................'
        self.reset_code_id_mapping(conn)        
        #for code in self.code2id:print code
        for nth,quote in enumerate(quotes):    
            #print u'导入第%d条:%s' % (nth+1,quote)
            #print quote.tstock
            if (nth + 1)%1000 == 1: 
                logger.debug(u'导入第%d条:%s',nth+1,quote)
            if quote.tstock in self.code2id:
                quote.tstock = self.code2id[quote.tstock]
                #print xsql % quote
                cursor.execute(xsql % quote)
                #to xdayquote
            else: #to sdayquote
                #cursor.execute(ssql % quote)
                self.add_squotes(conn,[quote],period=period)
        #cursor.close() #django文档中没有指出需要关闭这个cursor

    def add_hour_quotes(self,conn,quotes):
        self.add_quotes(conn,quotes,period='hour')

    def add_squotes_tuple(self,conn,quotes):   
        ''' 成批导入数据(tuple风格),容易出错
            
            quotes为元素是tcommon.Quote类型对象的列表
            source_id=1是分析家数据源
            由调用方决定事先是否需要先执行同时间段的清理工作
            这类直接的sql字符串实现的方式,性能上和安全性上不如类似于prepared statement的方式,但是消除了不同的驱动以及django自身所带来的parameters style不同的问题
        '''
        cursor = conn.cursor()
        sql = "insert into dune_sdayquote(sstock,tdate,topen,tclose,thigh,tlow,tavg,tvolume,tamount) values('%s',%d,%d,%d,%d,%d,%d,%d,%d)"
        for quote in quotes:
            #print sql % quote.to_tuple()
            cursor.execute(sql % quote.to_tuple())
        #cursor.close() #django文档中没有指出需要关闭这个cursor

    def transfer_xquotes(self,conn,begin=0,end=99999999):
        '''从DayQuote向XDayQuote倒数据.
        
            [begin,end)为时间区间
            1为分析家数据源. 必须保证sdayquote的sstock等于标准代码
            由调用方决定事先是否需要先执行同时间段的清理工作
        '''
        cursor = conn.cursor()
        cursor.execute("insert into dune_xdayquote(tstock_id,tdate,topen,tclose,thigh,tlow,tavg,tvolume,tamount) "
        " select ds.id,tdate,topen,tclose,thigh,tlow,tavg,tvolume,tamount"
        " from dune_sdayquote sq"
        " join dune_stockcode ds on ds.code=sq.sstock"
        " where ds.id in (select id from dune_stockcode where stype_id in('STOCK','FUND','INDEX')) "
        " and tdate between %s and %s - 1",(begin,end))

    def transfer_xquotes2(self,conn,stocks,begin=0,end=22000101,period='day',dfunc=lambda d:d):
        '''从DayQuote向XDayQuote倒数据.
            
            stocks为指定的标准代码的code集合,对这些code,不再判断是否是合适的stock type
            [begin,end)为时间区间
            1为分析家数据源. 必须保证sdayquote的sstock等于标准代码
            由调用方决定事先是否需要先执行同时间段的清理工作
        '''
        cursor = conn.cursor()
        str_stocks = '(%s)' % ','.join(["'%s'" % stock for stock in stocks])  #如果stocks为空，则为(),也符合sql语法
        cursor.execute("insert into dune_x"+period+"quote(tstock_id,tdate,topen,tclose,thigh,tlow,tavg,tvolume,tamount) "
        " select ds.id,tdate,topen,tclose,thigh,tlow,tavg,tvolume,tamount"
        " from dune_s"+period+"quote sq "
        " join dune_stockcode ds on ds.code = sq.sstock " 
        " where sq.sstock in " + str_stocks + " and tdate between %s and %s - 1 " ,(dfunc(begin),dfunc(end)))

    def transfer_hour_xquotes2(self,conn,stocks,begin=0,end=22000000):
        self.transfer_xquotes2(conn,stocks,begin,end,period='hour',dfunc=lambda d:d*100)

    find_squotes_sql = "select sstock as code,tdate,topen,tclose,thigh,tlow,tavg,tvolume,tamount"    \
              " from dune_sdayquote"    \
              " where tdate between %s and %s - 1 "
    order_by = " order by code,tdate"

    def get_squotes(self,conn,begin=0,end=99999999):
        '''检索指定时间范围内的原始行情数据

            [begin,end)为时间区间
            1为分析家数据源.
            返回值为{source_code:records}这样的字典
        '''
        quotes = self.query(conn,(self.find_squotes_sql + self.order_by) % (begin,end))
        return self.organize_tuples2quotes2(quotes)

    def get_squotes2(self,conn,stocks,begin=0,end=99999999):
        '''检索指定时间范围内指定股票的原始行情数据

            stocks为source_code的列表
            [begin,end)为时间区间
            1为分析家数据源.
            返回值为{source_code:records}这样的字典
        '''
        condition = 'and sstock in (%s)' % ','.join(["'%s'" % stock for stock in stocks])  #如果stocks为空，则为(),也符合sql语法
        quotes = self.query(conn,(self.find_squotes_sql + condition + self.order_by) % (begin,end))
        return self.organize_tuples2quotes2(quotes)

    find_xquotes_sql = "select code,tdate,topen,tclose,thigh,tlow,tavg,cast(tvolume+0.5 as int),tamount"    \
              " from dune_xdayquote dx"    \
              " join dune_stockcode dc on dc.id = dx.tstock_id" \
              " where tdate between %s and %s - 1 " 
    def get_xquotes(self,conn,begin=0,end=99999999):
        '''检索制定时间范围内的已经除权的行情数据

           [begin,end)为时间区间
           返回值为{source_code:records}这样的字典   
        '''
        quotes = self.query(conn,(self.find_xquotes_sql + self.order_by) % (begin,end))
        return self.organize_tuples2quotes2(quotes)
        
    def get_xquotes2(self,conn,stocks,begin=0,end=99999999):
        '''检索制定时间范围内的已经除权的行情数据

           stocks为standard_code的列表
           [begin,end)为时间区间
           返回值为{source_code:records}这样的字典   
        '''
        #print stocks
        return self.organize_tuples2quotes2(self._fetch_xquotes2(conn,stocks,begin,end))

    def _fetch_xquotes2(self,conn,stocks,begin=0,end=99999999):
        '''检索制定时间范围内的已经除权的行情数据

           stocks为standard_code的列表
           [begin,end)为时间区间
           返回值为{source_code:records}这样的字典   
        '''
        condition = 'and code in (%s)' % ','.join(["'%s'" % stock for stock in stocks])  #如果stocks为空，则为(),也符合sql语法        
        #print condition
        quotes = self.query(conn,(self.find_xquotes_sql + condition + self.order_by) % (begin,end))
        #print len(quotes)
        return quotes

    def ref_sql(self,period='day'):
        return '''select dx.topen,dx.tclose,dx.thigh,dx.tlow,dx.tavg,dx.tamount,cast(dx.tvolume+0.5 as int)
            from dune_x'''+period+'''quote base 
            left join dune_x'''+period+'''quote dx on  dx.tdate=base.tdate and dx.tstock_id = %(stock_id)s
            where base.tstock_id=%(base_id)s and base.tdate between %(begin)s and %(end)s
            order by base.tdate
        ''' #volume中的数据可能为非整型，需要转换

    def get_refbased_xquotes(self,conn,base_id,stock_id,begin=0,end=99999999):
        ''' 获取基于ref的行情数据，其中base_id为参考序列的id，stock_id为实际序列的id
            返回的序列长度为base_id代表的序列在该时间段内的长度
            base.tdate不必要出现
        '''
        end -= 1    #满足[begin,end)的惯例
        sql = self.ref_sql('day') % locals()
        logging.debug('get_refbased_xquotes:%s' % sql)        
        return self.query(conn,sql)

    def get_refbased_hour_xquotes(self,conn,base_id,stock_id,begin=0,end=99999999):
        ''' 获取基于ref的小时行情数据，其中base_id为参考序列的id，stock_id为实际序列的id
            返回的序列长度为base_id代表的序列在该时间段内的长度
            base.tdate不必要出现
        '''
        begin = begin * 100
        end = end * 100
        end -= 1    #满足[begin,end)的惯例        
        sql = self.ref_sql(period='hour') % locals()
        logging.debug('get_refbased_hour_xquotes:%s' % sql)
        return self.query(conn,sql)

    def add_stock_to_catalog(self,conn,name,subject,stocks):
        '''向板块中添加成员

            name: 板块名. 如果板块不存在，则会创建该板块
            subject: 板块类别名
            stocks: 标准stockcode列表，为板块成员
            调用者需要确保stocks中的成员都不是板块的老成员，否则会抛出IntegrityError
        '''
        cs = CatalogSubject.objects.get(name=subject)
        nc,created = Catalog.objects.get_or_create(name=name,defaults={'subject':cs})
        #nc,created = Catalog.objects.get_or_create(name=name,subject=cs)  #这样会抱错DoesNotExist: Catalog matching query does not exist.，貌似是给的条件太多了，name本身是unique的. 也可能是django自己的问题
        condition = ' code in (%s)' % ','.join(["'%s'" % stock for stock in stocks])  #如果stocks为空，则为(),也符合sql语法        
        sql = "insert into dune_stockcode_catalogs(stockcode_id,catalog_id)" \
                    " select ds.id,'%s'"  \
                    " from dune_stockcode ds "  \
                    " where %s "    \
                    % (nc.id,condition)
        cursor = conn.cursor()
        cursor.execute(sql)

    def xit_old(self,conn,xinfos):
        '''反向除权(20090518之前的除权方式)

        
         xinfos为PowerInfo对象的列表。必须满足xinfo.state=0. 里面不检验
         反向除权(即当前价不变，更动除权前的价量)计算公式
         按照成交金额不变的模式来计算
         	配送股价格：cur = (old + pgbl*pgj)/(1+sgbl+pgbl)
         	    我们现在以1/1000作为pgbl和sgbl的基本单位
         	    实际上是  (old*1000 + pgj*pgbl) /(1000+pgbl+sgbl)
         	分红价格：cur = old - fhbl
            因为配送股和分红同时的话，则先分红再送配，所以组合之后有:
                newprice = ((oldprice-fh)*1000 + pgj*pgbl) /(1000 + pgbl + sgbl)
                         = (oldprice-fh) * (1000/(100+pgbl+sgbl)) + pgj*pgbl/(1000+pgbl+sgbl)
                设 x = 1000/(1000+pgbl+sgbl)
                   y = pgj*pgbl/(1000+pgbl+sgbl)
                则 newp_rice = (oldprice - fh) * x + y
          Amount保持不变, new_volume=amount/new_avg_price
        '''
        cursor = conn.cursor()
        for nth,xinfo in enumerate(xinfos):
            try:
                b = 1000.0 + xinfo.pgbl + xinfo.sgbl
                x = 1000/b
                y = xinfo.pgj * xinfo.pgbl / b
                if (nth+1)% 10 == 1: print u'%d : %s除权，executeday:%d,x=%s,y=%s,fhbl=%s' % (nth,xinfo.tstock.code,xinfo.execute_day,x,y,xinfo.fhbl)
                sql = "update dune_xdayquote set topen = cast((topen - %(fhbl)s)*%(x)s+%(y)s + 0.5 as int),tclose = cast((tclose - %(fhbl)s)*%(x)s + %(y)s + 0.5 as int),thigh = cast((thigh - %(fhbl)s)*%(x)s + %(y)s + 0.5 as int),tlow = cast((tlow - %(fhbl)s)*%(x)s + %(y)s + 0.5 as int),tavg = cast((tavg-%(fhbl)s)*%(x)s + %(y)s + 0.5 as int),tvolume = cast(tavg * tvolume/((tavg-%(fhbl)s)*%(x)s + %(y)s) + 0.5 as int) where tstock_id=%(stock)s and tdate < %(end)s"  \
                % {'stock':xinfo.tstock.id,'end':xinfo.execute_day,'fhbl':xinfo.fhbl,'x':x,'y':y}
                #print sql
                cursor.execute(sql)
                xinfo.day_state = 1
                #print 'save xinfo',xinfo.tstock.code,xinfo.execute_day
                xinfo.save()
            except sqlite3.IntegrityError,inst:
                logger.warning(u'股票 %s 除权出错' % xinfo.tstock.code)

    def xit(self,conn,xinfos,period='day',dfunc=lambda d:d):
        '''反向的比例除权
            
         xinfos为PowerInfo对象的列表。必须满足xinfo.state=0. 里面不检验
         反向除权(即当前价不变，更动除权前的价量)计算公式
         以除权日的前一个交易日收盘价为基准计算比例
                oldprice = 基准日价格
                newprice = ((oldprice-fh)*1000 + pgj*pgbl) /(1000 + pgbl + sgbl)
                         = (oldprice-fh) * (1000/(100+pgbl+sgbl)) + pgj*pgbl/(1000+pgbl+sgbl)
                设 x = 1000/(1000+pgbl+sgbl)
                   y = pgj*pgbl/(1000+pgbl+sgbl)
                则 newprice = (oldprice - fh) * x + y
                复权比例 xrate = newprice / oldprice
         这样，所有的除权日之前的价格，都按照 price * xrate 来计算
         而Amount保持不变, new_volume=amount/new_avg_price=amount/(old_avg*xrate) = old_volume / xrate
        '''
        cursor = conn.cursor()
        state_name = period + '_state'
        for nth,xinfo in enumerate(xinfos):
            try:
                b = 1000.0 + xinfo.pgbl + xinfo.sgbl
                x = 1000/b
                y = xinfo.pgj * xinfo.pgbl / b
                if (nth+1)% 10 == 1: 
                    print u'%d : %s除权，executeday:%d,x=%s,y=%s,fhbl=%s' % (nth,xinfo.tstock.code,xinfo.execute_day,x,y,xinfo.fhbl)
                ori_close = self.get_latest_xquote_close(conn,xinfo.tstock.id,xinfo.execute_day)
                new_close = (ori_close - xinfo.fhbl) * x + y
                if ori_close > 0 and new_close > 0:
                    xrate = new_close / ori_close
                else:
                    xrate = 1
                    logger.warning(u'警告，ori_close=0或new_close=0,stock_id=%s,execute_day=%s,ori_close=%s,new_close=%s,b=%s,x=%s,y=%s,fhbl=%s' % (xinfo.tstock.id,xinfo.execute_day,ori_close,new_close,b,x,y,xinfo.fhbl))
                #print xrate,ori_close,new_close
                sql = "update dune_x"+period+"quote set topen = cast(topen * %(xrate)s + 0.5 as int),tclose = cast(tclose * %(xrate)s + 0.5 as int),thigh = cast(thigh * %(xrate)s + 0.5 as int),tlow = cast(tlow * %(xrate)s + 0.5 as int),tavg =cast(tavg * %(xrate)s + 0.5 as int),tvolume = cast(tvolume / %(xrate)s + 0.5 as int) where tstock_id=%(stock)s and tdate < %(end)s" \
                % {'stock':xinfo.tstock.id,'end':dfunc(xinfo.execute_day),'xrate':xrate}
                cursor.execute(sql)                
                #print sql
                #xinfo.state = 1
                setattr(xinfo,state_name,1)
                #print 'save xinfo',xinfo.tstock.code,xinfo.execute_day,sql
                xinfo.save()
            except sqlite3.IntegrityError,inst:
                logger.warning(u'股票 %s 除权出错' % xinfo.tstock.code)
    
    def xit_hour(self,conn,xinfos): #小时除权
        self.xit(conn,xinfos,period='hour',dfunc=lambda d:d*100)

    def gbjg(self,conn):    #股本结构
        cursor = conn.cursor()
        sql = '''select x.sid,tpm,dr.zgb,dr.ag from (select tstock_id as sid,ag,max(tperiod) as tpm from dune_report  group by tstock_id) as x,dune_report dr on dr.tstock_id = x.sid and dr.tperiod=tpm'''
        jgs = self.query(conn,sql)
        rev = []
        for jg in jgs:
            dobj = DataObject()
            dobj.id = jg[0]
            dobj.date = jg[1]
            dobj.zgb = jg[2]
            dobj.ag = jg[3]
            rev.append(dobj)
        return rev


class NormalStore(SqlStore):
    '''django实现的部分
    '''

    def __init__(self):
        from django.db import connection
        SqlStore.__init__(self,connection)

    def add_stock_to_catalog2(self,name,stocks):
        '''向板块中添加成员

            name: 板块名. 必须存在该板块
            stocks: 标准stockcode列表，为板块成员
            插入前stocks中成员若已经属于该板块,无异常. 相当于noop(pass)操作
        '''
        #print name
        catalog = Catalog.objects.get(name=name)    #如果板块不存在，这里会抛出异常
        stock_set = StockCode.objects.all()
        stockcodes = set(stock_set.values_list('code')) #每个元素为(code,)形式的元组
        #[stock_set.get(code = code).catalogs.add(catalog) for code in stocks if (code,) in stockcodes]
        [catalog.stocks.add(stock_set.get(code = code)) for code in stocks if (code,) in stockcodes]


    def get_stockmap(self):
        try:
            self.stockmap
        except AttributeError:
            self.stockmap = dict([(stock.code,stock) for stock in list(StockCode.objects.all())])
        rev = self.stockmap
        return rev

    def add_stock_to_catalog3(self,name,stocks):
        '''向板块中添加成员.  2太慢,故作优化

            name: 板块名. 必须存在该板块
            stocks: 标准stockcode列表，为板块成员
            插入前stocks中成员若已经属于该板块,无异常. 相当于noop(pass)操作
        '''
        #print name
        catalog = Catalog.objects.get(name=name)    #如果板块不存在，这里会抛出异常
        #stockmap = dict([(stock.code,stock) for stock in list(StockCode.objects.all())])
        stockmap = self.get_stockmap()  #stockmap移出后速度加快1倍
        [catalog.stocks.add(stockmap[code]) for code in stocks if code in stockmap]


    def add_mappings(self,mappings,source):
        '''添加源-标准的映射表(映射表不更新). 这块功能现在已经弱化，只为导入时source_code无法根据规则转化为standard的提供帮助

            mappings为(source_code,StockCode对象)的列表
            source为数据源
        '''
        for scode,sobj in mappings:
            mapping = Mapping.objects.create(source_code = scode,standard=sobj,source=source)

    def update_codes(self,codes):
        '''更新代码表, 实质上code是不能更新的，更新以后只能算新股票

            codes:   元素为(exchange,code,name,stocktype)的列表
            返回添加的代码列表
        '''
        code_query_set = StockCode.objects.all()        
        code_set =  set([ code[0] for code in code_query_set.values_list('code')])
        new_codes = [ code for code in codes if code[1] not in code_set ]
        exist_codes = [ (code[1],code[2]) for code in codes if code[1] in code_set ]
        for exchange,code,name,stype in new_codes:
            new_corp = Corporation.objects.create(name=name)
            stock = StockCode.objects.create(corporation = new_corp,code = code,name = name, exchange=exchange,stype=stype)
            StockCodeHistory.objects.create(scode=stock,name=name)
        for code,name in exist_codes:
            exist_obj = StockCode.objects.get(code=code)
            if exist_obj.name != name:
                exist_obj.name = name
                exist_obj.save()
                StockCodeHistory.objects.create(scode=stock,name=name)                
        return [ code[1] for code in new_codes ]
    
            
    def update_codes2(self,pairs,exchange,stype):
        '''更新代码表, 这个早先的接口性能较好，但操作太不自然，废弃

            pairs:   元素为(code,name)的列表
            exchage: 交易所对象
            stype:   证券类型
            返回添加的(代码,对象)列表

        '''
        codes = StockCode.objects.filter(exchange=exchange,stype=stype).values_list('code','name')
        codeset = {}
        codeset.update(codes)
        print codeset
        new_codes = []
        for code,name in pairs:
            if code not in codeset:
                new_corp = Corporation.objects.create(name=name)
                codes = StockCode.objects.filter(exchange=exchange,stype=stype).values_list('code','name')
                new_obj = StockCode.objects.create(code = code,name = name,corporation = new_corp,exchange=exchange,stype=stype)
                new_codes.append((code,new_obj))
            elif codeset[code] != name:
                exist_obj = StockCode.objects.get(code=code)
                exist_obj.name = name
                exist_obj.save()
        return new_codes

    def add_xinfos(self,xinfos):
        ''' 添加除权数据

            xinfos为tcommon.XInfo类型的记录列表,其tstock为source_code
        '''
        code2obj = self.get_stockmap()
        xinfos = [xinfo for xinfo in xinfos if xinfo.tstock in code2obj]
        for x in xinfos:
            pi = PowerInfo(tstock=code2obj[x.tstock],sgbl=x.tsgbl,pgbl=x.tpgbl,fhbl=x.tfhbl,zfs=x.tzfs,  \
                    zfj=x.tzfj,jjs=x.tjjs,register_day=x.tregisterday,execute_day=x.texecuteday)
            pi.save()

    def add_reports(self,reports):
        ''' 添加report
            reports: 元素为tcommon.Report类型的列表
            字段变换:tperiod = r.tdate            
        '''
        code2obj = self.get_stockmap()
        reports = [report for report in reports if report.tstock in code2obj]
        for r in reports:
            stock = code2obj[r.tstock]
            rt = Report(tstock=stock,announce_day=r.treleaseday, \
                    tperiod=r.tdate,zgb=r.tzgb,ag=r.tag,bg=r.tbg,hg=r.thg,    \
                    mgwfplr=r.tmgwfplr,mggjj=r.tmggjj,mgjzc=r.tmgjzc,mgsy=r.tmgsy)
            rt.save()
            self.update_stock_size(stock,r.tzgb,r.tag)

    def update_stock_size(self,stock,zgb,ag,edate=0):
        if stock.total_size != zgb or stock.outstanding_size != ag:
            stock.total_size = zgb
            stock.outstanding_size = ag
            stock.save()
            StockCodeHistory.objects.create(scode=stock,name=stock.name,total_size=zgb,outstanding_size=ag)
            return True
        return False
        
    def get_xinfo_keys(self,begin,end):
        ''' 找出时间段内的除权纪录键,
            
            返回(source_code,execute_day)组成的列表
        '''
        xinfos = PowerInfo.objects.filter(execute_day__range=(begin,end-1)).order_by('execute_day') \
            .values_list('tstock__id','execute_day')
        smap = self.get_id2code()
        return [ (smap[xinfo[0]],xinfo[1]) for xinfo in xinfos ]

    def get_report_keys(self,begin,end):
        ''' 找出时间段内的报表纪录键
            
            返回tcommon.XInfo列表,每个纪录的tstock以code表示
        '''
        reports = Report.objects.filter(announce_day__range=(begin,end-1)).order_by('tperiod') \
            .values_list('tstock__id','tperiod')
        smap = self.get_id2code()
        return [ (smap[report[0]],report[1]) for report in reports ]


